package cn.Mmovie.dao.Impl;

import cn.Mmovie.bean.Auditorium;
import cn.Mmovie.bean.Cinema;
import cn.Mmovie.bean.Movie;
import cn.Mmovie.bean.Schedule;
import cn.Mmovie.dao.ScheduleDao;
import cn.Mmovie.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ScheduleDaoImpl implements ScheduleDao {
    @Override
    public Integer insert(Schedule schedule) {
        Integer result = -999;
        try {
            Connection conn = DbUtil.getConnection();
            String sql = "INSERT INTO screening_schedule(movie_id,auditorium_id,start_time,start_date) " +
                    "VALUES(?,?,?,?)";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, schedule.getMovie().getId());
            psmt.setInt(2, schedule.getAuditorium().getId());
            psmt.setTime(3, schedule.getStartTime());
            psmt.setDate(4, schedule.getStartDate());
            result = psmt.executeUpdate();
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Integer delete(Integer id) {
        Integer result = -999;

        try {
            //获取数据库连接对象
            Connection conn = DbUtil.getConnection();
            //创建预处理命令对象
            String sql = "DELETE FROM screening_schedule WHERE schedule_id=?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            //设置传入参数
            psmt.setInt(1, id);
            //执行操作，返回影响行数
            result = psmt.executeUpdate();
            //释放资源
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return result;
    }

    @Override
    public Integer update(Schedule schedule) {
        Integer result = -999;

        try {
            Connection conn = DbUtil.getConnection();
            String sql = "UPDATE screening_schedule SET movie_id=?,auditorium_id=?,start_time=?,start_date=? WHERE schedule_id=?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, schedule.getMovie().getId());
            psmt.setInt(2, schedule.getAuditorium().getId());
            psmt.setTime(3, schedule.getStartTime());
            psmt.setDate(4, schedule.getStartDate());
            psmt.setInt(5, schedule.getId());
            result = psmt.executeUpdate();
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Schedule selectById(Integer id) {
        Schedule schedule = null;

        try {
            //获取数据库连接对象
            Connection conn = DbUtil.getConnection();
            //创建预处理命令对象
            String sql = "SELECT * FROM screening_schedule WHERE schedule_id=?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            //设置传入参数
            psmt.setInt(1, id);
            //执行操作，返回查询结果集
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                schedule = new Schedule();
                schedule.setId(rs.getInt("schedule_id"));
                schedule.setMovie(new Movie(rs.getInt("movie_id")));
                schedule.setAuditorium(new Auditorium(rs.getInt("auditorium_id")));
                schedule.setStartTime(rs.getTime("start_time"));
                schedule.setStartDate(rs.getDate("start_date"));
            }
            //释放资源
            DbUtil.close(rs, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return schedule;
    }

    @Override
    public List<Schedule> selectAll() {
        return null;
    }

    @Override
    public List<Schedule> selectForPageItems(Integer offset, Integer rows) {
        List<Schedule> schedules = null;
        try {
            Connection conn = DbUtil.getConnection();
            String sql = "SELECT s.schedule_id sid,s.movie_id smovie_id,s.auditorium_id sauditorium,s.start_time sstart_time,s.start_date sstart_date," +
                    "m.id mid,m.name mname,m.date mdate,m.director mdirect,m.picture mpicture,a.id aid,a.name aname,a.capacity acapacity" +
                    " FROM screening_schedule s LEFT JOIN movie m ON s.movie_id=m.id LEFT JOIN auditorium a ON s.auditorium_id=a.id ORDER BY a.id DESC LIMIT ?,?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, offset);
            psmt.setInt(2, rows);

            ResultSet rs = psmt.executeQuery();
            schedules = new ArrayList<>();
            while (rs.next()) {
                Auditorium auditorium = new Auditorium(rs.getInt("aid"), new Cinema(), rs.getString("aname"), rs.getInt("acapacity"));
                Schedule schedule = new Schedule();
                Movie movie = new Movie(rs.getInt("mid"), rs.getString("mname"), rs.getDate("mdate"), auditorium, rs.getString("mdirect"), rs.getString("mpicture"));
                schedule.setId(rs.getInt("sid"));
                schedule.setAuditorium(auditorium);
                schedule.setMovie(movie);
                schedule.setStartTime(rs.getTime("sstart_time"));
                schedule.setStartDate(rs.getDate("sstart_date"));
                schedules.add(schedule);
            }
            System.out.println(schedules);
            DbUtil.close(rs, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return schedules;
    }

    @Override
    public Integer selectForRecordTotal() {
        Integer result = null;
        try {
            Connection conn = DbUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM screening_schedule ORDER BY schedule_id DESC";
            PreparedStatement psmt = conn.prepareStatement(sql);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return result;
    }
}
